﻿CREATE PROCEDURE [dbo].[spCreateTransactions] ( @OwnerID int )
AS
	set nocount on

	declare @RetryDelay int
	declare @RetryCount int
	declare @AllowPartialShipments bit
	declare @CashAccountID int
	declare @TransactionID int
	declare @AccountID int

	select @RetryDelay = RetryCardDelay, 
		@RetryCount = RetryCardCount, 
		@AllowPartialShipments = AllowPartialShipments,
		@CashAccountID = CashAccountID
	from tblOwner 
	where OwnerID = @OwnerID

	-- Cancel orders which have been retried too many times.
	update oh set OrderStatusCode = 'C', CanceledReasonID = 2, CanceledDate = GETDATE()
	from tblOrderHeader oh
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and ( select COUNT(*) from tblTransaction where OrderID = oh.OrderID
			and TransactionTypeCode = 'B'
			and TransactionStatusCode = 'F'
			) >= @RetryCount

	-- Allocate inventory for pending orders
	declare aic cursor for select oh.OrderID -- AllocateInventoryCursor
	from tblOrderHeader oh
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and floor(cast(oh.ProjShipDate as float)) <= FLOOR(cast(getdate() as float))
	declare @OrderID int
	open aic
	fetch next from aic into @OrderID
	while @@FETCH_STATUS = 0
	begin
		exec spAllocateInventory @OrderID, @AllowPartialShipments

		fetch next from aic into @OrderID
	end
	close aic
	deallocate aic

	-- Update the order status to Paid for orders which have an order total of 0.
	update oh set OrderStatusCode = 'E'
	from tblOrderHeader oh
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and floor(cast(oh.ProjShipDate as float)) <= FLOOR(cast(getdate() as float))
		and oh.OrderTotal = 0

	-- Select pending orders that don't already have a recent transaction created for them
	declare ctc cursor for -- CreateTransactionCursor
	select oh.OrderID
	from tblOrderHeader oh
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and floor(cast(oh.ProjShipDate as float)) <= FLOOR(cast(getdate() as float))
		and (oh.OrderTotal = 0 or
			( not exists ( select 1 from tblTransaction where OrderID = oh.OrderID
					and TransactionTypeCode = 'B'
					and TransactionStatusCode in ( 'E', 'I' ) )
				and not exists ( select 1 from tblTransaction where OrderID = oh.OrderID
					and TransactionTypeCode = 'B'
					and TransactionStatusCode = 'E'
					and ResponseDate >= DATEADD(day, 0 - @RetryDelay, FLOOR(cast(getdate() as float))))
			))
	open ctc
	fetch next from ctc into @OrderID
	while @@FETCH_STATUS = 0
	begin
		insert into tblTransaction ( TransactionTypeCode, Amount, CreateDate, TransactionStatusCode, BankCheckNumber, OrderID, RetryCount )
		select 'B', OrderTotal, GETDATE(), 'E', BankCheckNumber, @OrderID, 
			( select COUNT(*) from tblTransaction where OrderID = @OrderID and TransactionTypeCode = 'B' and TransactionStatusCode = 'F' ) + 1
		from tblOrderHeader 
		where OrderID = @OrderID

		set @TransactionID = SCOPE_IDENTITY()

		insert into tblTransactionDetail ( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'C', AccountID, OrderTotal
		from tblOrderHeader 
		where OrderID = @OrderID
		
		insert into tblTransactionDetail ( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'D', @CashAccountID, OrderTotal
		from tblOrderHeader 
		where OrderID = @OrderID
		
		fetch next from ctc into @OrderID
	end
	close ctc
	deallocate ctc

	-- Allocate transactions for refunds
	declare rtc cursor for -- refund transaction cursor
	select r.OrderID, sum(r.Cost)
	from tblRefund r
		join tblOrderHeader oh on r.OrderID = oh.OrderID
	where r.TransactionID is null
		and oh.OwnerID = @OwnerID
	group by r.OrderID
	declare @Cost money
	open rtc
	fetch next from rtc into @OrderID, @Cost
	while @@FETCH_STATUS = 0
	begin
		select top 1 @AccountID = td.AccountID
		from tblTransaction t
			join tblTransactionDetail td on t.TransactionID = td.TransactionID and td.TransactionDetailTypeCode = 'C'
		where OrderID = @OrderID
			and TransactionTypeCode = 'B'
			and TransactionStatusCode = 'P'

		insert into tblTransaction ( TransactionTypeCode, Amount, CreateDate, TransactionStatusCode, OrderID, RetryCount )
		select 'R', @Cost, GETDATE(), 'E', @OrderID, 0

		set @TransactionID = SCOPE_IDENTITY()

		insert into tblTransactionDetail ( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'D', @AccountID, @Cost
		
		insert into tblTransactionDetail ( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'C', @CashAccountID, @Cost
		
		update tblRefund set TransactionID = @TransactionID
		where OrderID = @OrderID
			and TransactionID is null

		fetch next from rtc into @OrderID, @Cost
	end
	close rtc
	deallocate rtc
RETURN 0